package com.cnlxu.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;


/**
 * Created by cnlxu on 2016/8/8.
 */
public class DBManager {

    private DBHelper helper;
    private SQLiteDatabase db;
    public DBManager(Context context){
        Log.i(DBHelper.TAG, "DBManger");
        helper = new DBHelper(context);
        db = helper.getWritableDatabase();
    }

    public WordInfo add( String word,String translation, String details, String more){
        WordInfo wordInfo = new WordInfo();
        if(queryByName(word)._id != -1){
            Log.d(DBHelper.TAG, word + "has existed.");
            return wordInfo;
        }

        Log.i(DBHelper.TAG, "--add data--" + word);
        ContentValues cv = new ContentValues();
        cv.put(DBHelper.TABLE_COLUMN.NAME, word);
        cv.put(DBHelper.TABLE_COLUMN.TRANSLATION, translation);
        cv.put(DBHelper.TABLE_COLUMN.MORE, more);
        Calendar calendar = Calendar.getInstance();
        calendar.setFirstDayOfWeek(Calendar.MONDAY);
        cv.put(DBHelper.TABLE_COLUMN.WEEK, calendar.WEEK_OF_YEAR);
        cv.put(DBHelper.TABLE_COLUMN.COUNTER, "0");
        cv.put(DBHelper.TABLE_COLUMN.STATUS, "0");
        cv.put(DBHelper.TABLE_COLUMN.DATE, "1");

        db.insert(DBHelper.DB_TABLE_NAME, null, cv);
        Cursor cursor = db.rawQuery("select last_insert_rowid() from "
                + DBHelper.DB_TABLE_NAME, null);

        if(cursor.moveToFirst()) {
            wordInfo._id = cursor.getInt(0);
            wordInfo.name = word;
            wordInfo.translation = translation;
            wordInfo.week = calendar.WEEK_OF_YEAR;
            wordInfo.counter = 0;
            wordInfo.status = 0;
        }
        return wordInfo;
    }


    public WordInfo query(String sql){
        Log.d(DBHelper.TAG, sql);
        Cursor c = db.rawQuery(sql, null);
        WordInfo info = new WordInfo();
        while (c.moveToNext()) {
            info._id = c.getInt(c.getColumnIndex(DBHelper.TABLE_COLUMN.ID));
            info.name = c.getString(c.getColumnIndex(DBHelper.TABLE_COLUMN.NAME));
            info.translation = c.getString(c.getColumnIndex(DBHelper.TABLE_COLUMN.TRANSLATION));
            info.week = c.getInt(c.getColumnIndex(DBHelper.TABLE_COLUMN.WEEK));
            info.counter = c.getInt(c.getColumnIndex(DBHelper.TABLE_COLUMN.COUNTER));
            info.date = c.getString(c.getColumnIndex(DBHelper.TABLE_COLUMN.DATE));
            break;
        }
        c.close();
        Log.i(DBHelper.TAG,"queried "+info.name+" _ID "+info._id);
        return info;
    }

    public WordInfo queryById(int id){
        String sql = "SELECT * FROM "+DBHelper.DB_TABLE_NAME + " where "
                +DBHelper.TABLE_COLUMN.ID+" = '"+id+"';";

        return query(sql);
    };

    public WordInfo queryByName(String word){
        String sql = "SELECT * FROM "+DBHelper.DB_TABLE_NAME + " where "
                +DBHelper.TABLE_COLUMN.NAME+" = '"+word+"';";
        return query(sql);
    }

    //TODO:
    public ArrayList<WordInfo> queryByWeek(int week){
        /*
        * long endTime = startTime + 24 * 3600 * 1000;

    现在就可以查询了：

    Cursor cursor = db.rawQuery("SELECT * FROM Journal WHERE “ +
        ”writeTime>=? and writeTime<?",
        new String[] { String.valueOf(startTime), String.valuesOf(endTime) });
*/
        String sql = "SELECT * FROM "
                + DBHelper.DB_TABLE_NAME;
        /*


                + " where "+ DBHelper.TABLE_COLUMN.WEEK
                + " = '"+week+"'";
 */
        ArrayList<WordInfo> results = new ArrayList<>();
        GregorianCalendar cal = new GregorianCalendar();
        cal.setTime(new Date());
        //可以根据需要设置时区
         //cal.setTimeZone(TimeZone.getDefault());
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        //毫秒可根据系统需要清除或不清除
        cal.set(Calendar.MILLISECOND, 0);
        long startTime = cal.getTimeInMillis();
        Cursor c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            WordInfo info = new WordInfo();
            info._id = c.getInt(c.getColumnIndex(DBHelper.TABLE_COLUMN.ID));
            info.name = c.getString(c.getColumnIndex(DBHelper.TABLE_COLUMN.NAME));
            info.translation = c.getString(c.getColumnIndex(DBHelper.TABLE_COLUMN.TRANSLATION));
            info.week = Integer.parseInt(c.getString(c.getColumnIndex(DBHelper.TABLE_COLUMN.WEEK)));
            info.more = c.getString(c.getColumnIndex(DBHelper.TABLE_COLUMN.MORE));
            info.date = c.getString(c.getColumnIndex(DBHelper.TABLE_COLUMN.DATE));
            info.counter = Integer.parseInt(c.getString(c.getColumnIndex(DBHelper.TABLE_COLUMN.COUNTER)));
            results.add(info);
        }
        c.close();
        return results;
    }
}
